package stardust.product.platform.persistence;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


public class JdbcHelper
{
	
	private Connection connection;

	public JdbcHelper()
	{
		
	}
	
	public JdbcHelper(Connection connection)
	{
		this.connection = connection;
	}

	public Connection getConnection()
	{
		return connection;
	}

	public void setConnection(Connection connection)
	{
		this.connection = connection;
	}
	
	private void checkConnection() throws PersistenceException, SQLException
	{
		if (this.connection == null)
		{
			throw new PersistenceException("没有指定数据库连接。");
		}
		else if (this.connection.isClosed())
		{
			throw new PersistenceException("与数据库的连接已经断开。");
		}
	}
	
	public int execute(String sql, Object... args) throws PersistenceException
	{
		PreparedStatement pst = null;

		try
		{
			this.checkConnection();
			
			System.out.println(sql);
			pst = this.connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++)
			{
				pst.setObject(i + 1, args[i]);
			}
			
			return pst.executeUpdate();
		}
		catch(SQLException e)
		{
			throw new PersistenceException(e);
		}
		finally
		{	
			try
			{
				if (pst != null)
				{
					pst.close();
				}
			}
			catch(SQLException e)
			{
				throw new PersistenceException(e);
			}
		}
	}
	
	public List<Map<String, Object>> query(String sql, Object... args) throws PersistenceException
  {
    PreparedStatement pst = null;
    ResultSet rs = null;
    
    try
    {
      this.checkConnection();

      System.out.println(sql);
      pst = this.connection.prepareStatement(sql);
      for (int i = 0; i < args.length; i++)
      {
        pst.setObject(i + 1, args[i]);
      }
      
      rs = pst.executeQuery();
      
      return this.toList(rs);
    }
    catch(SQLException e)
    {
      throw new PersistenceException(e);
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
      }
      catch(SQLException e)
      {
        throw new PersistenceException(e);
      }
      
      try
      {
        if (pst != null)
        {
          pst.close();
        }
      }
      catch(SQLException e)
      {
        throw new PersistenceException(e);
      }
    }
  }
	
	public Map<String, Object> get(String sql, Object... args) throws PersistenceException
  {
    PreparedStatement pst = null;
    ResultSet rs = null;
    
    try
    {
      this.checkConnection();

      System.out.println(sql);
      pst = this.connection.prepareStatement(sql);
      for (int i = 0; i < args.length; i++)
      {
        pst.setObject(i + 1, args[i]);
      }
      
      rs = pst.executeQuery();
      
      return this.toMap(rs);
    }
    catch(SQLException e)
    {
      throw new PersistenceException(e);
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
      }
      catch(SQLException e)
      {
        throw new PersistenceException(e);
      }
      
      try
      {
        if (pst != null)
        {
          pst.close();
        }
      }
      catch(SQLException e)
      {
        throw new PersistenceException(e);
      }
    }
  }
	
	public void queryWithCallback(String sql, QueryCallback callback, Object... args) throws PersistenceException
  {
    PreparedStatement pst = null;
    ResultSet rs = null;
    
    try
    {
      this.checkConnection();

      System.out.println(sql);
      pst = this.connection.prepareStatement(sql);
      for (int i = 0; i < args.length; i++)
      {
        pst.setObject(i + 1, args[i]);
      }
      
      rs = pst.executeQuery();
      
      this.toCallback(rs, callback);
    }
    catch(SQLException e)
    {
      throw new PersistenceException(e);
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
      }
      catch(SQLException e)
      {
        throw new PersistenceException(e);
      }
      
      try
      {
        if (pst != null)
        {
          pst.close();
        }
      }
      catch(SQLException e)
      {
        throw new PersistenceException(e);
      }
    }
  }
	
	public List<Map<String, Object>> toList(ResultSet resultSet) throws SQLException
  {
    ResultSet rs = null;
    ResultSetMetaData rsMeta = null;
    
    rs = resultSet;
    rsMeta = rs.getMetaData();
    
    List<Map<String, Object>> dataList = new LinkedList<Map<String, Object>>();
    Map<String, Object> data;
    
    while ((data = this.toMap(resultSet)) != null)
    {
      dataList.add(data);
    }
    
    return dataList;
  }
	
	public Map<String, Object> toMap(ResultSet resultSet) throws SQLException
	{
	  ResultSet rs = null;
    ResultSetMetaData rsMeta = null;
    
    rs = resultSet;
    rsMeta = rs.getMetaData();
    
    if (resultSet.next())
    {
      Map<String, Object> data;
      
      data = new HashMap<String, Object>();
      
      for (int i = 0; i < rsMeta.getColumnCount(); i++)
      {
        data.put(rsMeta.getColumnLabel(i + 1).toUpperCase(), rs.getObject(i + 1));
      }
      
      return data;
    }
    else
    {
      return null;
    }
	}
	
	private void toCallback(ResultSet resultSet, QueryCallback callback) throws SQLException
  {
    ResultSet rs = null;
    ResultSetMetaData rsMeta = null;
    
    rs = resultSet;
    rsMeta = rs.getMetaData();
    
    Map<String, Object> data;
    
    while (rs.next())
    {
      data = new HashMap<String, Object>();
      
      for (int i = 0; i < rsMeta.getColumnCount(); i++)
      {
        data.put(rsMeta.getColumnLabel(i + 1).toLowerCase(), rs.getObject(i + 1));
      }
      
      callback.onFetch(data);
    }
  }
	
	public void close(boolean rollback)
	{
		try
		{
			if (this.connection != null && !this.connection.isClosed())
			{
				try
				{
					if (rollback)
					{
						this.connection.rollback();
					}
					else
					{
						this.connection.commit();
					}
					
					this.connection.close();
				}
				catch (SQLException e)
				{
					;
				}
			}
		}
		catch (SQLException e)
		{
			;
		}
		
	}
	
}
